package EX02_Account;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class AccountDAO implements IAccountDAO{

	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:realdance/realdance@112.216.253.125:1521:xe";
	private String insertSQL = "insert into Account values (exam.val,?,?,sysdate)";
	private String selectSQL = "select * from Account order by ano desc";
	private String updateSQL = "update Account set balance=?, email=?, dateCreated = sysdate where ano = ? ";
	private String deleteSQL = "delete Account where ano = ?";
	
	
	@Override
	public boolean insertAccount(AccountVO a) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
	
		try{conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setInt(1, a.getAno());
			pstmt.setString(2, a.getAid());
			pstmt.setInt(3, a.getBalance());
			pstmt.setString(4, a.getDateCreated());
			pstmt.execute();
			result = true;
		
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn,pstmt,null);
		}
		return result;
		}
	private Connection openDatabase(){
		try{ Class.forName(DRV);
		return DriverManager.getConnection(URL);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	private void closeDatabase
	(Connection c, PreparedStatement p, ResultSet r)
	{	if(r != null)
		try{r.close(); r = null;}
	catch(Exception e){}
		if(p != null)
		try {p.close(); p = null;}
	catch(Exception e){}
		if(c != null)
		try {c.close(); c = null;}
		catch(Exception e){}
	}
			
	
	@Override
	public List<AccountVO> selectAccount() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<AccountVO> result = new ArrayList<>();
		
		try{
			conn = openDatabase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				AccountVO a = new AccountVO();
				a.setAno(rs.getInt("ano"));
				a.setAid(rs.getString("aid"));
				a.setBalance(rs.getInt("balance"));
				a.setEmail(rs.getString("Email"));
				result.add(a);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn, pstmt, rs);
			
		}
		return result;
	}
	@Override
	public boolean updateAccount(AccountVO a) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try { conn = openDatabase();
			pstmt = conn.prepareStatement(updateSQL);
			pstmt.setInt(1, a.getBalance());
			int cnt = pstmt.executeUpdate();
			if(cnt==0) System.out.println(
					"입금을 시도했으나, 입금에 실패했습니다.");
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn, pstmt, null);
		} return result;
	}	
	@Override
	public boolean deleteAccount(AccountVO a) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{conn = openDatabase();
		pstmt = conn.prepareStatement(deleteSQL);
		pstmt.setInt(1, a.getAno());
		int cnt = pstmt.executeUpdate();
		if(cnt == 0) System.out.println("명령은 실행 했으나, 삭제된 데이터는 없습니다.");
		result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatabase(conn, pstmt, null);
		}
		return result;
	}
	
	
}
